package com.roc.service.org;
import java.io.File;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.regex.Pattern;

import org.apache.log4j.LogManager;
import org.apache.log4j.Logger;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.roc.bean.ApplyChannel;
import com.roc.dao.DaoException;
import com.roc.dao.IBaseDao;
import com.roc.dao.ISQLBaseDao;
import com.roc.page.WhereHQLFieldBean;
import com.roc.pojo.Operators;
import com.roc.pojo.TblSettleItem;
import com.roc.service.IApplyMoneyService;
import com.roc.service.ServiceException;
import com.roc.util.Constance;
import com.roc.util.MD5Utils;
import com.roc.util.constant.SysLogType;
import com.roc.web.action.settlemorg.SettlementOrgMoneySearchCondition;
@Service
@Transactional
public class OrgMoneyServiceImpl implements IOrgMoneyService{
	private static Logger logger = LogManager.getLogger(OrgMoneyServiceImpl.class);
	
	@Autowired
	ISQLBaseDao sqlBaseDao;
	@Autowired
	private IBaseDao baseDao;
	@Override
	@Transactional(rollbackFor=ServiceException.class)
	public synchronized String applySettlementMoney(SettlementOrgMoneySearchCondition condition,Operators op) throws ServiceException {
		String msg="";//返回状态信息
		String channel="",remark="";
		//1.首先拼接条件语句(此处由于都是从主库进行操作，所以并没有使用原有的DG公共方法)
		String stitchSql="";
		List<Object> conditionList=new ArrayList<Object>();//用于查询
		try {
				if(Constance.isNotEmpty(condition.getChannel())){//通道
					channel=condition.getChannel().trim();
				}else{
					msg="出款通道不能为空";
					return msg;
				}
				if(Constance.isNotEmpty(condition.getRemark())){//备注
					remark=condition.getRemark().trim();
				}else{
					msg="请填写出款备注";
					return msg;
				}
				if(Constance.isNotEmpty(condition.getPhone())){//手机号
					stitchSql=stitchSql+" and a.mobile_no = ? ";
					conditionList.add(condition.getPhone().trim());
				}
				if(Constance.isNotEmpty(condition.getAccountNo())){//账号
					stitchSql=stitchSql+" and a.account_id = ? ";
					conditionList.add(condition.getAccountNo().trim());
				}		
				if(Constance.isNotEmpty(condition.getApplyDateMin())){//清算起始日期
					stitchSql=stitchSql+" and a.trans_dt >= ? ";
					conditionList.add(condition.getApplyDateMin().trim());
				}
				if(Constance.isNotEmpty(condition.getApplyDateMax())){//清算结束日期
					stitchSql=stitchSql+" and a.trans_dt <= ? ";
					conditionList.add(condition.getApplyDateMax().trim());
				}
				if(Constance.isNotEmpty(condition.getOrg())){//机构
					stitchSql=stitchSql+" and a.ins_id_cd = ? ";
					conditionList.add(condition.getOrg().trim());
				}
				if(Constance.isNotEmpty(condition.getNature())){//性质
					if("01".equals(condition.getNature())){
						stitchSql=stitchSql+"  and length(a.pye_acct_nm)>5 and a.pye_acct_nm not like '%·%' ";
					}else if("02".equals(condition.getNature())){
						stitchSql=stitchSql+" and (length(a.pye_acct_nm)<5 or a.pye_acct_nm like '%·%') ";
					}else if("03".equals(condition.getNature())){
						stitchSql=stitchSql+" and length(a.pye_acct_nm)=5 ";
					}
				}
				if(Constance.isNotEmpty(condition.getType())){//类型
					stitchSql=stitchSql+" and a.ma_trans_cd = ? ";
					conditionList.add(condition.getType().trim());
				}
				if(Constance.isNotEmpty(condition.getAmtMax())){
					stitchSql=stitchSql+" and a.remark5 = ? ";
					conditionList.add(condition.getCombingName());
				}
				//构造条件,方便以下可以共用
				Object[] parmas=new Object[conditionList.size()];
				for(int i=0;i<conditionList.size();i++){
					parmas[i]=conditionList.get(i);
				}
				//2.首先查出是否存在符合条件并且状态为‘1’结算完成的所有数据List
				String sql="select a.trans_dt,a.key_rsp,a.ins_id_cd,account_id,a.pay_amt,a.remark1 from apstfr.tbl_ins_pay_detail a where a.status='01' ";		
				sql=sql+stitchSql;//带条件的完整SQL
				List<Object> list=null;
					list=sqlBaseDao.queryAll(sql, parmas);
					if(list!=null && list.size()>0){
						double total=0;//出款金额
						int count=0;//出款笔数
						for(int i=0;i<list.size();i++){
							Object[] current=(Object[]) list.get(i);
							int effect=handleOriginATM(current, op, channel, remark);
							if(effect>0){
								count=count+1;
								total=total+Double.parseDouble(Constance.Null2EmptyTrim(current[4]));//累加
							}
						}
						//处理金额累计
						DecimalFormat df=new DecimalFormat("0.00");
						msg="请求"+count+"项出款成功，共计出款金额"+df.format(total)+"元";						
					}else{
						msg="没有符合条件的出款条目";
					}
		} catch (DaoException e) {
			logger.debug(e.getMessage());
			throw new ServiceException("请求出款出错~");
		}
		return msg;
	}
	
	@Override
	@Transactional(rollbackFor=ServiceException.class)
	public synchronized String applySettlementMoneySimple(SettlementOrgMoneySearchCondition condition,String[] id, Operators op) throws ServiceException {
		String msg="";//返回信息
		String channel="",remark="";
		try {
			if(Constance.isNotEmpty(condition.getChannel())){//通道
				channel=condition.getChannel().trim();
			}else{
				msg="出款通道不能为空";
				return msg;
			}
			if(Constance.isNotEmpty(condition.getRemark())){//备注
				remark=condition.getRemark().trim();
			}else{
				msg="请填写出款备注";
				return msg;
			}
			String sql="select a.trans_dt,a.key_rsp,a.ins_id_cd,account_id,a.pay_amt,a.remark1 from apstfr.tbl_ins_pay_detail a where a.status='01' and a.key_rsp=? ";		
			Object[] parmas=null;
			double total=0;//出款金额
			int count=0;//出款笔数
			for(int i=0;i<id.length;i++){
					parmas=new Object[1];
					parmas[0]=id[i];
					Object[] item=(Object[]) sqlBaseDao.findObj(sql, parmas);
					int effect=handleOriginATM(item, op, channel, remark);
					if(effect>0){
						count=count+1;
						total=total+Double.parseDouble(Constance.Null2EmptyTrim(item[4]));//累加
					}
				}
				//处理金额累计
				DecimalFormat df=new DecimalFormat("0.00");
				msg="请求"+count+"项出款成功，共计出款金额"+df.format(total)+"元";				
			}catch (DaoException e) {
				logger.debug(e.getMessage());
				throw new ServiceException("单独请求出款出错~");
			}
			return msg;
	}
	
	@Override
	public List<ApplyChannel> selectChannelList() throws ServiceException {
		List<ApplyChannel> channelList=null;
		try {
			String sql="select code,name from t_apply_channel";
			List<Object> list = sqlBaseDao.queryAll(sql);
			if(list.size()>0){
				channelList = new ArrayList<ApplyChannel>();
				for(int i=0;i<list.size();i++){
					Object[] obj=(Object[]) list.get(i);
					ApplyChannel channel=new ApplyChannel();
					channel.setCode(Constance.Null2EmptyTrim(obj[0]));
					channel.setName(Constance.Null2EmptyTrim(obj[1]));
					channelList.add(channel);
				}
			}
		} catch (DaoException e) {
			logger.debug(e.getMessage());
			throw new ServiceException("查询出款通道出错~");
		}
		return channelList;
	}

	@Override
	public synchronized Map<String, Object> handleCombineAmt(
			SettlementOrgMoneySearchCondition searchCondition,Operators op)
			throws ServiceException {
		Map<String, Object> map=new HashMap<String, Object>();
		String stitchSql="select a.key_rsp,a.pay_amt from apstfr.tbl_ins_pay_detail a where a.status='01' ";
		List<Object> conditionList=new ArrayList<Object>();//用于查询
		stitchSql=stitchSql+" and a.remark5 is null ";
		if(Constance.isNotEmpty(searchCondition.getAccountNo())){//账号
			stitchSql=stitchSql+" and a.account_id = ? ";
			conditionList.add(searchCondition.getAccountNo().trim());
		}		
		if(Constance.isNotEmpty(searchCondition.getPhone())){//手机号
			stitchSql=stitchSql+" and a.mobile_no = ? ";
			conditionList.add(searchCondition.getPhone().trim());
		}		
		if(Constance.isNotEmpty(searchCondition.getApplyDateMin())){//清算起始日期
			stitchSql=stitchSql+" and a.trans_dt >= ? ";
			conditionList.add(searchCondition.getApplyDateMin().trim());
		}
		if(Constance.isNotEmpty(searchCondition.getApplyDateMax())){//清算结束日期
			stitchSql=stitchSql+" and a.trans_dt <= ? ";
			conditionList.add(searchCondition.getApplyDateMax().trim());
		}
		if(Constance.isNotEmpty(searchCondition.getOrg())){//机构
			stitchSql=stitchSql+" and a.ins_id_cd = ? ";
			conditionList.add(searchCondition.getOrg().trim());
		}
		if(Constance.isNotEmpty(searchCondition.getNature())){//性质
			if("01".equals(searchCondition.getNature())){
				stitchSql=stitchSql+"  and length(a.pye_acct_nm)>5 and a.pye_acct_nm not like '%·%' ";
			}else if("02".equals(searchCondition.getNature())){
				stitchSql=stitchSql+" and (length(a.pye_acct_nm)<5 or a.pye_acct_nm like '%·%') ";
			}else if("03".equals(searchCondition.getNature())){
				stitchSql=stitchSql+" and length(a.pye_acct_nm)=5 ";
			}
		}
		if(Constance.isNotEmpty(searchCondition.getType())){//类型
			stitchSql=stitchSql+" and a.ma_trans_cd = ? ";
			conditionList.add(searchCondition.getType().trim());
		}
		
		Object[] parmas=new Object[conditionList.size()];
		for(int i=0;i<conditionList.size();i++){
			parmas[i]=conditionList.get(i);
		}
		//先出所有符合条件的，然后进行金额拼凑，塞进amtList里进行标记
		List<Object> list=null;
		List<String> amtList=new ArrayList<String>();//需要修改标志的部分
		try {
			list=sqlBaseDao.queryAll(stitchSql, parmas);
			if(list.size()>0){
				logger.info("开始拼凑金额");
				double temp=0,store=0;//临时值
				double max=Double.valueOf(searchCondition.getAmtMax())*10000;//最大值
				for(int i=0;i<list.size();i++){
					Object[] obj=(Object[]) list.get(i);//取出来
					double param=Double.valueOf(String.valueOf(obj[1]));//参数值
					temp=temp+param;
					if(max-temp>=0){
						store=store+param;
						amtList.add(String.valueOf(obj[0]));
					}else{
						break;
					}
				}
				logger.info("拼凑金额完成");
				//修改标志				
				logger.info("拼凑金额进行标记");
				String updateSQL=" update apstfr.tbl_ins_pay_detail set remark5=? where key_rsp=? and status='01' ";	
				for(String serialNO:amtList){
					List<Object> targetList=new ArrayList<Object>();
					targetList.add(op.getAccountNo());
					targetList.add(serialNO);
					sqlBaseDao.deleteBySql(updateSQL, targetList);				
				}
				logger.info("拼凑金额标记完成");				
				DecimalFormat df=new DecimalFormat("0.00");
				String msg="拼凑完成，已拼凑出"+amtList.size()+"条记录，累计金额:"+df.format(store)+"元";
				map.put("result",msg);
			}else{
				map.put("error","匹配符合条件的记录为空");
			}
		} catch (DaoException e) {
			logger.debug(e.getMessage(), e);
			throw new ServiceException("拼凑金额标记失败!");
		}
		return map;
	}

	@Override
	public void clearOriginCombie(SettlementOrgMoneySearchCondition searchCondition,Operators op) throws ServiceException {
		
		String updateSQL=" update apstfr.tbl_ins_pay_detail set remark5=null where remark5=? and status='01' ";	
		List<Object> targetList=new ArrayList<Object>();
		targetList.add(op.getAccountNo());
		if(Constance.isNotEmpty(searchCondition.getApplyDateMin())){//清算起始日期
			updateSQL=updateSQL+" and trans_dt >= ? ";
			targetList.add(searchCondition.getApplyDateMin().trim());
		}
		if(Constance.isNotEmpty(searchCondition.getApplyDateMax())){//清算结束日期
			updateSQL=updateSQL+" and trans_dt <= ? ";
			targetList.add(searchCondition.getApplyDateMax().trim());
		}
		try {
				sqlBaseDao.deleteBySql(updateSQL,targetList);
				logger.info("清空原有标记成功");
		} catch (DaoException e) {
			logger.debug(e.getMessage(), e);
			throw new ServiceException("清空原有标记失败!");
		}
	}
	
	/**
	 * 更改原公共方法
	 * @param item
	 * @param flag
	 * @param op
	 * @param channel
	 * @param remark
	 * @param total
	 * @param count
	 * @throws ServiceException 
	 */
	protected int handleOriginATM(Object[] item,Operators op,String channel,String remark) throws ServiceException{
		int status=0;	
		try {
				String updateOriginSQL=" update apstfr.tbl_ins_pay_detail set remark1=?,remark2=?,remark3=?,remark4=?,status='02',rec_upd_ts=systimestamp,remark5=null where key_rsp = ? and status='01' ";
				//新流水号=机构号+结算日期+账号+8位随机数+交易键值
				String originSerial=Constance.Null2EmptyTrim(item[5]);//原流水
				if("".equals(originSerial)){
					originSerial=Constance.Null2EmptyTrim(item[2])+MD5Utils.MD5Encode(Constance.Null2EmptyTrim(item[0])+Constance.Null2EmptyTrim(item[3])+Constance.randomNums(8)+Constance.Null2EmptyTrim(item[1])).toUpperCase();
				}									
				List<Object> lastParam=new ArrayList<Object>();
				lastParam.add(originSerial);
				lastParam.add(op.getAccountNo());
				lastParam.add(channel);
				lastParam.add(remark);
				lastParam.add(Constance.Null2EmptyTrim(item[1]));
				int rows=sqlBaseDao.deleteBySql(updateOriginSQL,lastParam);					
				if(rows>0){
						baseDao.addSyslog(SysLogType.APPLY_SETTLEMENT_MONEY.getCode(),SysLogType.APPLY_SETTLEMENT_MONEY.getName(), op.getAccountNo(),"操作员【"+op.getAccountNo()+"】申请了原流水为【"+originSerial+"】的出款项");
						status=1;
				}
			} catch (DaoException e) {
				logger.debug(e.getMessage(),e);
				throw new ServiceException("修改原出款项失败!");
			}
		return status;
	}
}
